Create PROC [dbo].[STILoadDrugInvoiceForSendToTaxSystem]
    @Startdate VARCHAR(10),
    @EndDate VARCHAR(10),
    @StartTime VARCHAR(10),
    @EndTime VARCHAR(10),
    @TypeFactor TINYINT,
    @ComputerName NVARCHAR(MAX),
    @InvoiceNumber VARCHAR(10)
AS
BEGIN
IF @InvoiceNumber = 0 
  SET @InvoiceNumber=NULL

    
    IF EXISTS
    (
        SELECT *
        FROM [tempdb].dbo.sysobjects
        WHERE name = '##CacheFactor'
              AND xtype = 'U'
    )
    BEGIN
        DELETE FROM [tempdb].dbo.##CacheFactor
        WHERE ComputerName = @ComputerName;
    END;
    ELSE
    BEGIN
        CREATE TABLE ##CacheFactor
        (
            Id BIGINT,
            InvoiceType TINYINT,
            InvoiceNumber VARCHAR(10) COLLATE Arabic_CI_AS,
            SenderName NVARCHAR(35) COLLATE Arabic_CI_AS,
            RecipientName NVARCHAR(35) COLLATE Arabic_CI_AS,
            TotalPrice MONEY,
            FactorDate VARCHAR(10) COLLATE Arabic_CI_AS,
            TimeFactor VARCHAR(5) COLLATE Arabic_CI_AS,
            IsSend BIT DEFAULT (1),
            DeductionFactor MONEY,
            IncrementFactor MONEY,
            ReceiptAndPaymentPrice MONEY,
            liquidationFactor MONEY,
            ComputerName NVARCHAR(MAX) COLLATE Arabic_CI_AS
        );
    END;


    INSERT INTO ##CacheFactor
    (
        Id,
        InvoiceType,
        InvoiceNumber,
        SenderName,
        RecipientName,
        TotalPrice,
        FactorDate,
        TimeFactor,
        IsSend,
		DeductionFactor,
		IncrementFactor,
		ReceiptAndPaymentPrice,
		liquidationFactor,
        ComputerName
    )
  SELECT theadh.Id_Havaleh,THeadh.Kind_Flag,THeadh.InvoiceNumber,THeadh.Trans_Name,THeadh.Resive_Name,
THeadh.TotalPrice,THeadh.FactorDate,THeadh.TimeFactor,THeadh.IsSend
,TOver_Under.DeductionFactor,TOver_Under.IncrementFactor,ReceiptAndPaymentPrice, (THeadh.TotalPrice + ISNULL(IncrementFactor - DeductionFactor, 0))
           - ISNULL(ReceiptAndPaymentPrice, 0) liquidationFactor,THeadh.ComputerName FROM (
			 SELECT Id_Havaleh,
			/* case 
			  WHEN Kind_Flag IN ( 1) THEN
                   0
               WHEN Kind_Flag IN (2 ) THEN
                   1
				   end as */
           Kind_Flag,
           CASE
               WHEN Kind_Flag IN ( 1) THEN
                   Nu_Forosh
               WHEN Kind_Flag IN (2 ) THEN
                   Nu_Bargasht_Forosh
           END AS InvoiceNumber,
           Trans_Name,
           Resive_Name,
           ( ISNULL(TotPriceTaghizat, 0) + ISNULL(TotPriceDiffer, 0) + ISNULL(TotPriceAzad, 0) 
		   + ISNULL(TotPriceKala, 0) + ISNULL(TotPriceOTC, 0) + ISNULL(TotPriceBimar, 0) 
                           + ISNULL(TotPriceSazman, 0) )
            AS TotalPrice,
           dbo.MiladiToShamsi( CONVERT(VARCHAR(10), Tarikh, 111) )AS FactorDate,
           CONVERT(VARCHAR(5), Tarikh, 108)TimeFactor,
           0 AS isSend,
           @ComputerName AS ComputerName,TotPriceDaryafti AS ReceiptAndPaymentPrice
    FROM Drugh WITH (NOLOCK)
     
    WHERE (Kind_Flag = @TypeFactor) 
	AND (kind_Save NOT IN ( 4,3))
          AND CONVERT(VARCHAR(10), Tarikh, 111)
          BETWEEN (@Startdate) AND (@EndDate)
          AND CONVERT(VARCHAR(10), Tarikh, 108)
          BETWEEN @StartTime AND @EndTime
		

          AND (NOT EXISTS
    (
        SELECT *
        FROM dbo.STITransaction WITH (NOLOCK)
        WHERE InvoiceId = dbo.drugh.Id_Havaleh
    )
              )
) AS THeadh
 LEFT OUTER JOIN 
			  (
			  SELECT Drugh.Id_Havaleh,
                   SUM(   CASE
                              WHEN Type = 1 THEN
                                  ROUND(Price, 0)
                              ELSE
                                  0
                          END
                      ) IncrementFactor,
                   SUM(   CASE
                              WHEN Type = 0 THEN
                                  ROUND(Price, 0)
                              ELSE
                                  0
                          END
                      ) DeductionFactor
            FROM Over_Under_Factor_Drug WITH (NOLOCK)
                INNER JOIN Drugh
                    ON Drugh.Id_Havaleh = Over_Under_Factor_Drug.Id_Havaleh
            GROUP BY Drugh.Id_Havaleh

			  )AS TOver_Under
ON TOver_Under.Id_Havaleh = THeadh.Id_Havaleh


WHERE InvoiceNumber = ISNULL(@InvoiceNumber, InvoiceNumber)  
END;
